/**
 * All code in teDB created by Jon.
 */

package Classes;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;
//Jonthan Axford

public class teDB {
	Connection conn;
	/**
	 * Initializes the DB parameters to make a connection
	 * @param driver Database Driver String
	 * @param connString Connection string to the database
	 * @param user Username or tablespace name
	 * @param pass Password
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public teDB(String driver, String connString, String user, String pass) throws ClassNotFoundException, SQLException
	{
		Class.forName(driver);//database driver
		conn = DriverManager.getConnection(connString, user, pass);
	}
	/**
	 * 
	 * @param table Table Name
	 * @param sep Sep is the separator character, allows you to make a string you can parse
	 * @return Returns an array of string results, each array entry is a line in a table
	 * @throws SQLException
	 */
	public String[] getAll(String table, char sep) throws SQLException
	{
		String row ="";
		ArrayList<String> results = new ArrayList<String>();
		ResultSet rs = getResultSet(table, "*", "1");
		while(rs.next())
		{
			for(int i=1; i <=rs.getMetaData().getColumnCount(); i++)
				row += rs.getString(i) + sep;
			results.add(row);
			row="";
		}
		
		return (String[]) results.toArray();
	}
	/**
	 * 
	 * @param table Table name
	 * @param cols	What columns you want, as a comma separated value string
	 * @return Returns a DB result set
	 * @throws SQLException
	 */
	public ResultSet getResultSet(String table, String cols, String where) throws SQLException
	{
		Statement mySatement = conn.createStatement();//SQL statement
		ResultSet rs = mySatement.executeQuery("select "+cols+" from " + table+ " where " + where);//omg query
		
		return rs;
	}
	public ResultSet getCustomRS(String query) throws SQLException
	{
		Statement mySatement = conn.createStatement();
		ResultSet rs = mySatement.executeQuery(query);//omg query		
		return rs;
	}
	/**
	 * 
	 * @param table Which table to update
	 * @param fields Array of field titles
	 * @param values Array of values for the fields 
	 * @param where String of where clauses 
	 * @return true if successful, false otherwise
	 * @throws SQLException
	 */
	public boolean update(String table, String[] fields, String[] values, String where) throws SQLException
	{
		if(fields.length!=values.length)
			return false;
		else
		{
			String statement ="UPDATE "+table+" set ";
			for(int i=0; i < fields.length-1; i++)
				statement += fields[i]+" = ?, ";
			statement += fields[fields.length-1] + " = ? where " + where;
			java.sql.PreparedStatement ps = conn.prepareStatement(statement);
			for(int i=0; i < values.length; i++)
				ps.setString(i+1, values[i]);
			int numrows = ps.executeUpdate();
			conn.commit();
			
			if(numrows==0)
				return false;
			return true;
		}
	}
	/**
	 * 
	 * @param from Table name
	 * @param where Where clause, without the where
	 * @return true if rows were deleted, else false
	 * @throws SQLException
	 */
	public boolean delete(String from, String where) throws SQLException
	{
		java.sql.PreparedStatement ps = conn.prepareStatement("DELETE FROM "+from+" WHERE "+where);
		int numrows = ps.executeUpdate();
		conn.commit();
		
		if(numrows==0)
			return false;
		return true;
	}
	/**
	 * 
	 * @param table Table Name
	 * @param values Array of value strings, must be in order that they will be inserted in
	 * @return True if successfull, false otherwise
	 * @throws SQLException
	 */
	public boolean insert(String table, String[] values) throws SQLException
	{
		String val="INSERT INTO "+table+" VALUES (";
		for(int i=0; i < values.length-1; i++)
			val+="?, ";
		val+="?)";
		java.sql.PreparedStatement sqlstmt = conn.prepareStatement(val);
		for(int i=0; i < values.length; i++)
		{
			sqlstmt.setString(i+1, values[i]);
		}
		boolean numrows = sqlstmt.execute();
		conn.commit();
		if(numrows)
			return false;
		return true;
	}
	/**
	 * 
	 * @param table which table are we getting this for
	 * @param keyField field name where the pk is
	 * @return returns the highest number pk + 1
	 * @throws SQLException 
	 */
	public String getGenKey(String table, String keyField) throws SQLException
	{
		//Functionality yoinked and modified from Dagny's code
		int newID=-1;
		Statement stmnt = conn.createStatement();
		ResultSet rs = stmnt.executeQuery("select max("+ keyField +") from " + table);
		while (rs.next())
		{
			//grab the last number and add one to it to get next number
			newID = (rs.getInt(1) + 1);
		}
		conn.commit();
		
		return Integer.toString(newID);
	}
	public void close() throws SQLException
	{
		conn.close();
	}
	
}
